import { MigrationInterface, QueryRunner } from "typeorm";

export class RemoveProductStockFields1701011234567
	implements MigrationInterface
{
	public async up(queryRunner: QueryRunner): Promise<void> {
		// 移除product表中的stock和availableStock字段
		await queryRunner.query(`ALTER TABLE products DROP COLUMN IF EXISTS stock`);
		await queryRunner.query(
			`ALTER TABLE products DROP COLUMN IF EXISTS available_stock`
		);
	}

	public async down(queryRunner: QueryRunner): Promise<void> {
		// 恢复stock和availableStock字段
		await queryRunner.query(
			`ALTER TABLE products ADD COLUMN stock INT DEFAULT 0`
		);
		await queryRunner.query(
			`ALTER TABLE products ADD COLUMN available_stock INT DEFAULT 0`
		);

		// 更新stock和availableStock的值
		await queryRunner.query(`
            UPDATE products p 
            SET stock = (
                SELECT COALESCE(SUM(ps.stock), 0) 
                FROM product_skus ps 
                WHERE ps.product_id = p.id
            ),
            available_stock = (
                SELECT COALESCE(SUM(ps.available_stock), 0) 
                FROM product_skus ps 
                WHERE ps.product_id = p.id
            )
        `);
	}
}
